<HTML>
<HEAD><TITLE>Confirmation Page</TITLE><HEAD>
<BODY>
<%@ page import="java.sql.*"%>
<%
	Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
                Class.forName("org.postgresql.Driver");
                conn = DriverManager.getConnection(
                    "jdbc:postgresql://localhost/Admissions?" +
                    "user=postgres&password=27428572");
%>
<%
	conn.setAutoCommit(false);
	pstmt = conn.prepareStatement("INSERT INTO addresses (street, city, areaCode, zipCode, phoneno, state, teleCode) VALUES (?,?,?,?,?,?,?)");
        pstmt.setString(1, session.getAttribute("street").toString());
        pstmt.setString(2, session.getAttribute("city").toString());
	pstmt.setString(3, session.getAttribute("areaCode").toString());
	pstmt.setString(4, session.getAttribute("zipCode").toString());
	pstmt.setInt(5, Integer.parseInt(session.getAttribute("number").toString()));
	if(session.getAttribute("residence").equals("United States")) {
		pstmt.setString(6, session.getAttribute("state").toString());
		pstmt.setInt(7, 0);
	} else {
		pstmt.setString(6, "none");
		pstmt.setInt(7, Integer.parseInt((String)session.getAttribute("code")));
	}
	pstmt.executeUpdate();
	Statement stmt5 = conn.createStatement();
	Statement stmt6 = conn.createStatement();
	rs = stmt5.executeQuery("SELECT id FROM addresses WHERE street = '" + session.getAttribute("street") + "' AND city = '" + session.getAttribute("city")			 + "'");
	try{
		ResultSet spec = stmt6.executeQuery("SELECT id FROM specialization WHERE name = '" + session.getAttribute("specialization") + "'");
		pstmt = conn.prepareStatement("INSERT INTO student (name,residence,citizenship,address,specialization) VALUES (?,?,?,?,?)");
		pstmt.setString(1, ((String)session.getAttribute("fullName")));
		pstmt.setString(2, ((String)session.getAttribute("residence")));
		pstmt.setString(3, ((String)session.getAttribute("citizenship")));
		if(rs.next())
		{
		pstmt.setInt(4, rs.getInt(1));
		}
		if(spec.next())
		{
		pstmt.setInt(5, spec.getInt(1));
		}
		pstmt.executeUpdate();
	}
	catch(Exception e){
		e.printStackTrace();
	}
			
	String sCount = (String)session.getAttribute("count");
	int count = Integer.parseInt(sCount);
	Statement stmt2 = conn.createStatement();
	Statement stmt3 = conn.createStatement();
	Statement stmt4 = conn.createStatement();
	Statement stmt7 = conn.createStatement();
	ResultSet student = stmt7.executeQuery("SELECT id FROM student WHERE name = '" + session.getAttribute("fullName") + "' AND residence = '" + session.getAttribute("residence") + "'");
	for(int i = 0; i < count; i++)
	{
		String stringCount = Integer.toString(i+1);
		pstmt = conn.prepareStatement("INSERT INTO disciplines (name, gpa, expectedDate, title) VALUEs (?,?,?,?)");
		pstmt.setString(1, (session.getAttribute("discipline"+stringCount).toString()));
		pstmt.setInt(2, Integer.parseInt((String)session.getAttribute("gpa"+stringCount)));
		pstmt.setInt(3, Integer.parseInt((String)session.getAttribute("awardedYear"+stringCount)));
		pstmt.setString(4, ((String)session.getAttribute("title"+stringCount)));
		pstmt.executeUpdate();
		ResultSet country = stmt2.executeQuery("SELECT id FROM Countries WHERE name = '" + session.getAttribute("location"+stringCount) + "'");
		ResultSet uni = stmt3.executeQuery("SELECT id FROM Universities WHERE name = '" + session.getAttribute("universities"+stringCount) + "'");
		ResultSet dd = stmt4.executeQuery("SELECT id FROM Disciplines WHERE name= '" + session.getAttribute("discipline"+stringCount) + "'");	 
		pstmt = conn.prepareStatement("INSERT INTO degrees (student,country,dicipline,university) VALUES (?,?,?,?)");
		if(student.next())
		{
		pstmt.setInt(1, student.getInt(1));
		}
		if(country.next())
		{
		pstmt.setInt(2, country.getInt(1));
		}
		if(dd.next())
		pstmt.setInt(3, dd.getInt(1));
		if(uni.next())
		pstmt.setInt(4, uni.getInt(1));
		
	}			
                conn.commit();
                conn.setAutoCommit(true);
        }
	catch (SQLException e){
		e.printStackTrace();
		throw new RuntimeException(e);
	}
	finally {
		if(rs != null){
			try{ 
				rs.close();
			} catch (SQLException e){ }
			rs = null;
		}
		if(pstmt != null) {
			try{
				pstmt.close();
			} catch(SQLException e){}
			pstmt = null;
		}
		if(conn != null){
			try{
				conn.close();
			}catch (SQLException e) { }
			conn = null;
		}
		}
%>

</BODY>
</HTML>
